# -*- coding: utf-8 -*-
#人员证书字典维护
import json


#查看字典列表
def getPersonCertDictList(request):
    request.cursor_standard.execute(request.formatSql('''
        select * 
        from standard.dict_person_apt 
        where qualifi_type like '%${key}%'
        or qualifi_name like '%${key}%'
        or qualifi_major like '%${key}%'
        or zgmc like '%${key}%'
        order by qualifi_type,qualifi_name
    '''))
    rows = request.cursor_standard.fetchall()
    return json.dumps(rows, ensure_ascii=False)


def insertPersonCertDict(request):
    request.cursor_standard.execute(request.formatSql('''
        insert into standard.dict_person_apt (qualifi_type,qualifi_name,qualifi_major,zgmc,which_no)
        values('${cert_name}','${profession}','${person_type}','','${which_no}')
    '''))
    return str(request.cursor_standard.rowcount)

#修改字典
def updatePersonCertDict(request):
    request.cursor_standard.execute(request.formatSql('''
        update standard.dict_person_apt 
        set qualifi_type = '${qualifi_type}',
        qualifi_name = '${qualifi_name}',
        qualifi_major = '${qualifi_major}' ,
        zgmc = '${zgmc}',
        which_no = '${which_no}'
        where cert_dict_id = ${cert_dict_id}
    '''))
    return str(request.cursor_standard.rowcount)


#别名列表
def getAliasList(request):
    request.cursor_standard.execute(request.formatSql('''
        select * 
        from standard.dict_person_apt_alias 
        where cert_dict_id = ${cert_dict_id} 
        order by data_source
    '''))
    rows = request.cursor_standard.fetchall()
    return json.dumps(rows, ensure_ascii=False)


#添加别名
def insertAlias(request):
    request.cursor_standard.execute(request.formatSql('''
        insert into standard.dict_person_apt_alias (
            cert_dict_id,
            cert_name_alias,
            profession_alias,
            data_source
        )values(
            ${cert_dict_id},
            '${cert_name_alias}',
            '${profession_alias}',
            '${data_source}'
        )
    '''))
    return str(request.cursor_standard.rowcount)


#修改别名
def updateAlias(request):
    request.cursor_standard.execute(request.formatSql('''
        update standard.dict_person_apt_alias 
        set cert_name_alias = '${cert_name_alias}',
        profession_alias = '${profession_alias}',
        data_source = '${data_source}' 
        where alias_id = ${alias_id}
    '''))
    return str(request.cursor_standard.rowcount)

#删除别名
def deleteAlias(request):
    request.cursor_standard.execute(request.formatSql('''
        delete from standard.dict_person_apt_alias
        where alias_id = ${alias_id}
    '''))
    return str(request.cursor_standard.rowcount)

#删除未匹配列表中已经匹配的
def delteAlreadyMatched(request):
    request.cursor_standard.execute(request.formatSql('''
        -- delete from middle.dbo.person_cert_un_match where data_source_code  = '${data_source_code}'
    '''))
    return str(request.cursor_standard.rowcount)


#获取未匹配列表
def getUnMatchList(request):
    #1数据
    request.cursor_standard.execute(request.formatSql('''
        select distinct 
            a.cert_type,
            a.profession,
            b.data_source_name  
        from middle.person_apt_un_match   a
        left join dict_data_source b on a.data_source_code = b.data_source_code  
        order by data_source_name,cert_type,profession 
        limit  ${start} , ${rows} 
    '''))
    rows = request.cursor_standard.fetchall()
    #2页码
    request.cursor_standard.execute(request.formatSql('''
        select count(*) as total 
        from (
            select distinct 
                a.cert_type,
                a.profession,
                b.data_source_name  
            from middle.person_apt_un_match   a 
            left join dict_data_source b  on  a .data_source_code = b.data_source_code   
        ) as a
    '''))
    total = request.cursor_standard.fetchone()['total']
    return json.dumps({'rows':rows,'total':total}, ensure_ascii=False)